--a
SELECT	D.oevelse, D.resultat 
FROM 	SN_DELTAKELSE AS D 
WHERE 	D.utoever=5;


--b
SELECT	O.navn, O.verdensrekord 
FROM 	SN_OEVELSE AS O 
WHERE 	O.verdensrekord=O.banerekord;

--c
SELECT	D.resultat 
FROM 	SN_DELTAKELSE AS D, SN_UTOEVER AS U 
WHERE 	U.navn='Vebjoern Rodal' AND D.oevelse='800m'
GROUP BY U.navn, D.oevelse, D.resultat;

--d
SELECT	U.startnr, U.navn, D.oevelse
FROM 	SN_UTOEVER AS U, SN_DELTAKELSE AS D, SN_OEVELSE AS O 
WHERE 	U.startnr=D.utoever AND O.navn=D.oevelse AND ( O.disiplin='loep' OR O.disiplin='sprang' )
GROUP BY U.startnr, U.navn, D.oevelse;

--e
SELECT	U.startnr, U.navn, D.oevelse
FROM 	SN_UTOEVER AS U, SN_DELTAKELSE AS D, SN_OEVELSE AS O
WHERE 	U.startnr=D.utoever AND O.navn=D.oevelse AND O.disiplin='kast'
GROUP BY U.startnr, U.navn, D.oevelse;


--f
SELECT	U.navn, D.resultat
FROM 	SN_UTOEVER AS U, SN_DELTAKELSE AS D
WHERE 	U.startnr=D.utoever AND D.oevelse='Spydkast'
GROUP BY U.navn, D.resultat
ORDER BY D.resultat DESC 
LIMIT 1;


--g
SELECT	U.navn, D.resultat
FROM 	SN_UTOEVER AS U, SN_DELTAKELSE AS D
WHERE 	D.oevelse='Hoeydehopp' AND D.utoever=U.startnr
GROUP BY U.navn, D.resultat 
ORDER BY D.resultat DESC;


--h
SELECT U.startnr, U.navn
FROM SN_UTOEVER AS U, SN_DELTAKELSE AS D 
WHERE U.startnr=D.utoever 
GROUP BY U.startnr, U.navn
HAVING COUNT( D.oevelse ) = 1
ORDER BY U.startnr ASC
;


--i

SELECT U.startnr, U.navn, COUNT( D.oevelse )
FROM SN_UTOEVER AS U, SN_DELTAKELSE AS D, (
	SELECT COUNT( D.oevelse ) AS CLC
	FROM SN_UTOEVER AS U, SN_DELTAKELSE AS D
	WHERE U.navn='Carl Lewis' AND U.startnr=D.utoever 
	GROUP BY U.startnr
	) AS CL
WHERE U.startnr=D.utoever AND  U.navn!='Carl Lewis'
GROUP BY U.startnr, U.navn, CL.CLC
HAVING COUNT( D.oevelse ) = CL.CLC
ORDER BY U.startnr ASC
;

--j
SELECT	U.startnr, U.navn, COUNT( D.oevelse )
FROM SN_UTOEVER AS U, SN_DELTAKELSE AS D, (
	SELECT COUNT( D.oevelse ) AS cnt
	FROM SN_UTOEVER AS U, SN_DELTAKELSE AS D 
	WHERE U.startnr=D.utoever
	GROUP BY U.startnr, U.navn
	ORDER BY COUNT( D.oevelse ) DESC 
	LIMIT 1
	) AS mx
WHERE U.startnr=D.utoever 
GROUP BY U.startnr, U.navn, mx.cnt 
HAVING COUNT( D.oevelse ) = mx.cnt
ORDER BY U.startnr ASC
;


--k
SELECT U.startnr, U.navn, D.oevelse 
FROM 	SN_UTOEVER AS U, SN_DELTAKELSE AS D, (
	SELECT D.oevelse as oevelse
	FROM 	SN_UTOEVER AS U, SN_DELTAKELSE AS D 
	WHERE 	U.navn='Carl Lewis' AND U.startnr=D.utoever 
	) AS CL
WHERE 	D.oevelse=CL.oevelse AND U.navn!='Carl Lewis' AND U.startnr=D.utoever
GROUP BY U.startnr, U.navn, D.oevelse
;

-- denne var jo umulig
--l
SELECT U.startnr, U.nasjonalitet 
FROM 	SN_UTOEVER AS U, SN_DELTAKELSE AS D, SN_OEVELSE AS O, (
	SELECT COUNT( DISTINCT O.navn ) AS cnt 
	FROM 	SN_OEVELSE AS O 
	WHERE 	O.disiplin='loep' 
	) AS LOP
WHERE 	O.disiplin='loep' AND U.startnr=D.utoever AND D.oevelse=O.navn
GROUP BY U.startnr, U.nasjonalitet, LOP.cnt
HAVING COUNT( O.navn ) = LOP.cnt
;


--m

SELECT D.oevelse, COUNT( U.startnr ) AS cnt
FROM SN_UTOEVER AS U, SN_DELTAKELSE AS D 
WHERE U.startnr=D.utoever
GROUP BY D.oevelse 
ORDER BY cnt DESC
;





--n
	
SELECT U.startnr, U.navn 
FROM 	SN_UTOEVER AS U, SN_OEVELSE AS O, SN_DELTAKELSE AS D 
WHERE O.navn=D.oevelse AND D.utoever=U.startnr
GROUP BY U.startnr, U.navn 
HAVING COUNT(  DISTINCT O.disiplin )>1
;
	
	
	
	
	
	
	
	







